CREATE VIEW [dbo].[vCustomerExperiences]
AS
SELECT
    [OfferedCustomerExperienceKey] AS CustomerExperienceKey,
    [ExperienceDefinitionName] AS ExperienceName,
    [NumberOfUnits] AS Units,
    CASE WHEN [AllowChangesFlag] = '1' THEN [OfferedCustomerExperienceDate] ELSE [OfferingDate] END AS DateExp,
    [ProgramKey],
    [ProgramName] AS Program,
    [LocationName] AS Location,
    [RequiresGradeFlag],
    [OfferedCustomerExperienceGrade] AS Grade
FROM
    [dbo].[vOfferedCustomerExperience]
UNION ALL
SELECT
    [UnofferedCustomerExperienceKey] AS CustomerExperienceKey,
    [ExperienceDefinitionName] AS ExperienceName,
    [NumberOfUnits] AS Units,
    [ExperienceDefinitionDate] AS DateExp,
    [ProgramKey],
    [ProgramName] AS Program,
    [ExperienceDefinitionLocation] AS Location,
    [RequiresGradeFlag],
    [UnofferedCustomerExperienceGrade] AS Grade
FROM
    [dbo].[vUnofferedCustomerExperience]
UNION ALL
SELECT
    [UserDefinedCustomerExperienceKey] AS CustomerExperienceKey,
    [UserDefinedCustomerExperienceName] AS ExperienceName,
    [UserDefinedCustomerExperienceUnits] AS Units,
    [CompletionDate] AS DateExp,
    [ProgramKey],
    [ProgramName] AS Program,
    [UserDefinedCustomerExperienceLocation] AS Location,
    [RequiresGradeFlag],
    [Grade]
FROM
    [dbo].[vUserDefinedCustomerExperience]
UNION ALL
SELECT  
    [OfferedCustomerExperienceKey] AS CustomerExperienceKey,
    [CertificationModuleName] AS ExperienceName,
    [NumberOfUnits] AS Units,
    [OfferingDate] AS DateExp,
    [CertificationProgramKey] AS ProgramKey,
    [CertificationProgramName] AS Program,
    [LocationName] AS Location,
    [RequiresGradeFlag],
    [OfferedCustomerExperienceGrade] AS Grade
FROM
    [dbo].[vModuleOfferedCustomerExperience]

GO